import datetime
from DBUtils import *
def welcom():
    print("————————————————————————管家婆家庭记账软件————————————————————————")
    print("1、添加账务\t","2、编辑账户\t","3、删除账务\t","4、查询账务\t","5、退出系统")

def add_User(ID,cate,username,money,time,mean):
    sql = "insert into guanjia value (%s,%s,%s,%s,%s,%s)"
    param = [ID,cate,username,money,time,mean]
    insert(sql,param)

def time_s(time_i):
    if time_i.isdigit():
        var = time_i[0:4]
        var1 = time_i[4:6]
        var2 = time_i[6:8]

        month = str(datetime.datetime.now().month)
        day = str(datetime.datetime.now().day)
        if 1 <= int(month) <= 9:
            month = "0" + month
            if 1 <= int(day) <= 9:
                day = "0" + day
        now_t = str(datetime.datetime.now().year) + month + day
        if 20100000 < int(time_i) < int(now_t):
            if 1 <= int(var1) <= 12:
                # 闰年模式
                if int(var) % 4 == 0:
                    if int(var1) == 2:
                        if 1 <= int(var2) <= 29:
                            return time_i
                        else:
                            print("闰年2月为1-29，请重新输入！")
                            return 1
                    elif int(var1) == 1 or int(var1) == 3 or int(var1) == 5 or int(var1) == 7 or int(var1) == 8 or int(var1) == 10 or int(var1) == 12:
                        if 1 <= int(var2) <= 31:
                            return time_i
                        else:
                            print("每逢1,3,5,7,8,10,12月,每月日期应为1-31号,请重新输入！")
                            return 1
                    elif int(var1) == 4 or int(var1) == 6 or int(var1) == 9 or int(var1) == 11:
                        if 1 <= int(var2) <= 30:
                            return time_i
                        else:
                            print("每逢4,6,9,11月，每月日期应为1-30号，请重新输入！")
                            return 1
                elif int(var) % 4 != 0:
                    if int(var1) == 2:
                        if 1 <= int(var2) <= 28:
                            return time_i
                        else:
                            print("平年2月为1-28，请重新输入！")
                            return 1
                    elif int(var1) == 1 or int(var1) == 3 or int(var1) == 5 or int(var1) == 7 or int(var1) == 8 or int(var1) == 10 or int(var1) == 12:
                        if 1 <= int(var2) <= 31:
                            return time_i
                        else:
                            print("每逢1,3,5,7,8,10,12月,每月日期应为1-31号,请重新输入！")
                            return 1
                    elif int(var1) == 4 or int(var1) == 6 or int(var1) == 9 or int(var1) == 11:
                        if 1 <= int(var2) <= 30:
                            return time_i
                        else:
                            print("每逢4,6,9,11月，每月日期应为1-30号，请重新输入！")
                            return 1
            else:
                print("月份为1-12月，输入错误，请重新输入！")
                return 1
        else:
            print("输入不能超过当前日期！")
            return 1
    else:
        print("输入日期有误！")
        return 1

def change():
    print("1、查询所有\t2、按条件查询")
    num = input("请选择 :")
    if num == "1":
        sql = "select * from guanjia"
        print("ID\t\t类别\t\t账户\t\t金额\t\t时间\t\t说明")
        for i in select(sql,None,mode="all"):
            print(i[0],"\t\t",i[1],"\t\t",i[2],"\t\t",i[3],"\t\t",i[4],"\t\t",i[5])
    elif num == "2":
        tim_1 = input("请输入查询起始时间 :")
        tim_2 = input("请输入查询结束时间 :")
        var = tim_1[0:4]
        var1 = tim_1[4:6]
        var2 = tim_1[6:8]
        var3 = tim_2[0:4]
        var4 = tim_2[4:6]
        var5 = tim_2[6:8]
        sql = "select 时间 from guanjia"
        for i in select(sql,None,mode="all"):
            if datetime.date(int(var),int(var1),int(var2)) not in i and datetime.date(int(var3),int(var4),int(var5)) not in i:
                return 1
            else:
                print("ID\t\t类别\t\t账户\t\t金额\t\t时间\t\t说明")
                sql = "select 时间 from guanjia where 时间 = %s"
                if datetime.date(int(var),int(var1),int(var2)) < datetime.date(int(var3),int(var4),int(var5)):
                    if datetime.date(int(var),int(var1),int(var2)) in select(sql,tim_1,mode="one") and datetime.date(int(var3),int(var4),int(var5)) in select(sql,tim_2,mode="one"):
                        sql1 = "select * from guanjia where 时间 >= %s and 时间 <= %s"
                        param = [tim_1,tim_2]
                        for i  in select(sql1,param,mode="all"):
                            print(i[0], "\t\t", i[1], "\t\t", i[2], "\t\t", i[3], "\t\t", i[4], "\t\t", i[5])
                    else:
                        print("时间段不存在！")
                        return 1
                else:
                    print("您输入的时间有误！")
                    return 1
            break

    else:
        return 1

def del_User():
    num = input("请输入ID :")
    if num.isdigit():
        num = int(num)
        sql = "select ID from guanjia"
        if 1 <= num <= len(select(sql,None,mode="all")):
            sql = "delete from guanjia where ID = %s"
            delete(sql,num)
            sql1 = "ALTER TABLE guanjia DROP ID"
            sql2 = "ALTER TABLE guanjia ADD ID INT NOT NULL FIRST"
            sql3 = "ALTER TABLE guanjia MODIFY COLUMN ID INT NOT NULL AUTO_INCREMENT,ADD PRIMARY KEY(ID)"
            execute(sql1)
            execute(sql2)
            execute(sql3)
            print("删除账务成功！")
        else:
            print("您要查找的数据不存在！")
    else:
        print("输入错误，请重新输入！")


while True:
    welcom()
    num = input("请输入要操作的功能序号[1-5]")
    if num.isdigit():
        num = int(num)
        # 第一个添加账户
        if num == 1:
            ID = None
            cate = input("请输入类别 :")
            username = input("请输入账户 :")
            money = input("请输入金额 :")
            if money.isdigit():
                money = int(money)
            else:
                print("输入有误，请重新输入！")
                continue
            time_i = input("请输入日期")
            if time_s(time_i) == 1:
                continue
            else:
                time = time_s(time_i)
            mean = input("请输入说明 :")
            add_User(ID, cate, username, money, time, mean)
            print("添加成功！")
        # 第二个编辑账户
        elif num == 2:
            ID = input("请输入ID")
            if ID.isdigit():
                ID = int(ID)
                sql = "select ID from guanjia"
                if 1 <= ID <= len(select(sql, None,mode="all")):
                    cate = input("请输入新类别 :")
                    username = input("请输入新账户 :")
                    money = input("请输入新金额 :")
                    if money.isdigit():
                        money = int(money)
                    else:
                        print("输入有误，请重新输入！")
                        continue
                    time_i = input("请输入新日期")
                    if time_s(time_i) == 1:
                        continue
                    else:
                        time = time_s(time_i)
                    mean = input("请输入说明 :")
                    sql = "update guanjia set 类别 = %s,账户 = %s,金额 = %s,时间 = %s,说明 = %s where ID = %s"
                    param = [cate, username, money, time, mean,ID]
                    update(sql,param)
                else:
                    print("数据不存在！")
            else:
                print("输入错误，请重新输入！")
                continue
        # 第三个删除账务
        elif num == 3:
            del_User()
        # 第四个查询账务
        elif num == 4:
            if change() == 1:
                continue
            else:
                change()
        # 退出系统
        elif num == 5:
            print("感谢您本次使用，再见！")
            exit(0)
        else:
            print("您输入有误！")
    else:
        print("您输入有误！")
